using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace QuanLyKho.DAL
{
    class DAO
    {
        private static SqlConnection conn;
        private static SqlCommand cmd;
        String strCnn;
        public DAO()
        {
            //cai nay comment khi nop bai nhe
            //strCnn = readConnectionString();

            // con cai  nay thi un Comment 
            strCnn = @"server=ITPRO-ANHNT\PERFECT;database=QuanLyKho;uid=sa;pwd=123";
        }

        private void InitConnection()
        {
            // r = new AppSettingsReader();
            // string connectionString = r.GetValue("Main.ConnectionString", typeof(string)).ToString();
            conn = new SqlConnection(strCnn);
            conn.Open();
        }
        public void CloseConnection()
        {
            if (conn.State.ToString() == "Open")
                conn.Close();
        }

        protected void PrepareCommand(SqlCommand cmd, string strSQL, CommandType type, IList<SqlParameter> paramList)
        {
            if (conn == null || conn.State == ConnectionState.Closed)
                InitConnection();
            cmd.Connection = conn;
            cmd.CommandType = type;
            cmd.CommandText = strSQL;

            // Add parameter list into the command
            if (paramList != null)
                foreach (SqlParameter param in paramList)
                    cmd.Parameters.Add(param);
        }

        public SqlDataReader ExecuteQuery(string strSQL, CommandType type, IList<SqlParameter> paramList)
        {
            cmd = new SqlCommand();
            PrepareCommand(cmd, strSQL, type, paramList);
            // return cmd.ExecuteReader();

            SqlDataReader dr = cmd.ExecuteReader();


            // CloseConnection();
            return dr;

        }

        public int ExecuteNonQuery(string strSQL, CommandType type, IList<SqlParameter> paramList)
        {
            cmd = new SqlCommand();
            PrepareCommand(cmd, strSQL, type, paramList);
            int i = cmd.ExecuteNonQuery();
            // CloseConnection();

            return i;
            //return cmd.ExecuteScalar

        }

        public object ExecuteScalar(string strSQL, CommandType type, IList<SqlParameter> paramList)
        {
            cmd = new SqlCommand();
            PrepareCommand(cmd, strSQL, type, paramList);

            //return cmd.ExecuteScalar();
            object obj = cmd.ExecuteScalar();
            // CloseConnection();

            return obj;

        }

        public DataTable ExecuteQueryDataTable(string strSQL, CommandType ct, IList<SqlParameter> paramList)
        {
            cmd = new SqlCommand();
            PrepareCommand(cmd, strSQL, ct, paramList);
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            return dt;
        }
    }
}
